﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Text.RegularExpressions;

namespace AliCMS.SubscribeModule.OleDb
{
    public class MailSender : OleDbBase, Dal.IMailSender
    {
        public int GetInnerIDFromGuid(Guid guid)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.MailSender.ID, MapType(Alias.MailSender.ID)).Value = guid;
            Command.CommandText = "SELECT " + MapColumn(Alias.MailSender.InnerID) + " FROM " + MapTable(Alias.Objects.MailSender) + " WHERE " + MapColumn(Alias.MailSender.ID) + "=?";
            object result = Command.ExecuteScalar();
            if (result == null)
                return 0;
            else
                return (int)result;
        }
        public Guid GetGuidFromInnerID(object id)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.MailSender.InnerID, MapType(Alias.MailSender.InnerID)).Value = id;
            Command.CommandText = "SELECT " + MapColumn(Alias.MailSender.ID) + " FROM " + MapTable(Alias.Objects.MailSender) + " WHERE " + MapColumn(Alias.MailSender.InnerID) + "=?";
            object result = Command.ExecuteScalar();
            if (result == null)
                return Guid.Empty;
            else
                return (Guid)result;
        }
        public Dictionary<string, object> Get(object id)
        {
            Dictionary<string, object> result = new Dictionary<string, object>();

            StringBuilder sql = new StringBuilder("SELECT TOP 1");
            sql.Append(" " + MapColumn(Alias.MailSender.ID));
            sql.Append("," + MapColumn(Alias.MailSender.InnerID));
            sql.Append("," + MapColumn(Alias.MailSender.Name));
            sql.Append("," + MapColumn(Alias.MailSender.Email));
            sql.Append("," + MapColumn(Alias.MailSender.Account));
            sql.Append("," + MapColumn(Alias.MailSender.Password));
            sql.Append("," + MapColumn(Alias.MailSender.SMTPServer));
            sql.Append("," + MapColumn(Alias.MailSender.EnableSsl));
            sql.Append("," + MapColumn(Alias.MailSender.Port));
            sql.Append("," + MapColumn(Alias.MailSender.IsDeleted));
            sql.Append("," + MapColumn(Alias.MailSender.CreatorID));
            sql.Append("," + MapColumn(Alias.MailSender.Creator));
            sql.Append("," + MapColumn(Alias.MailSender.CreateTime));
            sql.Append("," + MapColumn(Alias.MailSender.UpdatorID));
            sql.Append("," + MapColumn(Alias.MailSender.Updator));
            sql.Append("," + MapColumn(Alias.MailSender.UpdateTime));
            sql.Append(" FROM " + MapTable(Alias.Objects.MailSender));

            if (id is Guid)
            {
                Command.CommandText = sql + " WHERE " + MapColumn(Alias.MailSender.ID) + "=?";

                Command.Parameters.Clear();
                Command.Parameters.Add("@" + Alias.MailSender.ID, MapType(Alias.MailSender.ID)).Value = id;
            }
            else
            {
                Command.CommandText = sql + " WHERE " + MapColumn(Alias.MailSender.InnerID) + "=?";

                Command.Parameters.Clear();
                Command.Parameters.Add("@" + Alias.MailSender.InnerID, MapType(Alias.MailSender.InnerID)).Value = id;
            }

            DataReader = Command.ExecuteReader();
            if (!DataReader.HasRows)
            {
                DataReader.Close();
                return null;
            }

            DataReader.Read();
            int i = 0;
            result[Alias.MailSender.ID] = DataReader[i++];
            result[Alias.MailSender.InnerID] = DataReader[i++];
            result[Alias.MailSender.Name] = DataReader[i++];
            result[Alias.MailSender.Email] = DataReader[i++];
            result[Alias.MailSender.Account] = DataReader[i++];
            result[Alias.MailSender.Password] = DataReader[i++];
            result[Alias.MailSender.SMTPServer] = DataReader[i++];
            result[Alias.MailSender.EnableSsl] = DataReader[i++];
            result[Alias.MailSender.Port] = DataReader[i++];
            result[Alias.MailSender.IsDeleted] = DataReader[i++];
            result[Alias.MailSender.CreatorID] = DataReader[i] == DBNull.Value ? Guid.Empty : DataReader[i]; i++;
            result[Alias.MailSender.Creator] = DataReader[i++];
            result[Alias.MailSender.CreateTime] = DataReader[i++];
            result[Alias.MailSender.UpdatorID] = DataReader[i] == DBNull.Value ? Guid.Empty : DataReader[i]; i++;
            result[Alias.MailSender.Updator] = DataReader[i++];
            result[Alias.MailSender.UpdateTime] = DataReader[i++];
            DataReader.Close();

            return result;
        }
        public void Add(Dictionary<string, object> item)
        {
            Command.Parameters.Clear();

            #region 排序
            if (!item.ContainsKey(Alias.MailSender.Sort))
            {
                Command.CommandText = "SELECT MAX(" + MapColumn(Alias.MailSender.Sort) + ") + 1 FROM " + MapTable(Alias.Objects.MailSender);
                object sort = Command.ExecuteScalar();
                if (sort == DBNull.Value)
                    item.Add(Alias.MailSender.Sort, 1);
                else
                    item.Add(Alias.MailSender.Sort, sort);
            }
            #endregion

            StringBuilder names = new StringBuilder();
            StringBuilder values = new StringBuilder();

            PrepareParameters(names, values, item);

            Command.CommandText = "INSERT INTO " + MapTable(Alias.Objects.MailSender) + "(" + names + ") VALUES(" + values + ")";
            Command.ExecuteNonQuery();
        }
        public void Modify(int id, Dictionary<string, object> changes)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.MailSender.InnerID, MapType(Alias.MailSender.InnerID)).Value = id;

            StringBuilder sql = new StringBuilder("UPDATE " + MapTable(Alias.Objects.MailSender) + " SET ");
            Command.Parameters.Clear();
            PrepareParameters(sql, changes);
            Command.Parameters.Add("@" + Alias.MailSender.InnerID, MapType(Alias.MailSender.InnerID)).Value = id;

            sql.Append(" WHERE " + MapColumn(Alias.MailSender.InnerID) + "=?");

            Command.CommandText = sql.ToString();
            Command.ExecuteNonQuery();
        }
        public void Move(int from, int to)
        {
            if (from == to)
                return;

            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.MailSender.InnerID, MapType(Alias.MailSender.InnerID));

            #region 获取to的排序值
            Command.CommandText = "SELECT " + MapColumn(Alias.MailSender.Sort) + " FROM " + MapTable(Alias.Objects.MailSender) + " WHERE " + MapColumn(Alias.MailSender.InnerID) + "=?";
            Command.Parameters["@" + Alias.MailSender.InnerID].Value = to;
            int toSort = Convert.ToInt32(Command.ExecuteScalar());
            #endregion

            #region 获取from的排序值
            Command.CommandText = "SELECT " + MapColumn(Alias.MailSender.Sort) + " FROM " + MapTable(Alias.Objects.MailSender) + " WHERE " + MapColumn(Alias.MailSender.InnerID) + "=?";
            Command.Parameters["@" + Alias.MailSender.InnerID].Value = from;
            int fromSort = Convert.ToInt32(Command.ExecuteScalar());
            #endregion

            if (fromSort > toSort)
            {
                #region to和大于他并且小于from的排序值都加1
                Command.CommandText = "UPDATE " + MapTable(Alias.Objects.MailSender) + " SET " + MapColumn(Alias.MailSender.Sort) + "=" + MapColumn(Alias.MailSender.Sort) + "+1 WHERE " + MapColumn(Alias.MailSender.Sort) + ">=" + toSort + " AND " + MapColumn(Alias.MailSender.Sort) + " < " + fromSort;
                Command.ExecuteNonQuery();
                #endregion

                #region from的排序值设置为toSort
                Command.CommandText = "UPDATE " + MapTable(Alias.Objects.MailSender) + " SET " + MapColumn(Alias.MailSender.Sort) + "=" + toSort + " WHERE " + MapColumn(Alias.MailSender.InnerID) + "=?";
                Command.ExecuteNonQuery();
                #endregion
            }
            /*
            else if (fromSort == toSort)
            {
                #region to和大于他的排序值都加1
                Command.CommandText = "UPDATE " + MapTable(Alias.Objects.MailSender) + " SET " + MapColumn(Alias.MailSender.Sort) + "=" + MapColumn(Alias.MailSender.Sort) + "+1 WHERE " + MapColumn(Alias.MailSender.Sort) + ">=" + toSort;
                Command.ExecuteNonQuery();
                #endregion
            }
            */
            else
            {
                #region to和小于他并且大于from的排序值都减1
                Command.CommandText = "UPDATE " + MapTable(Alias.Objects.MailSender) + " SET " + MapColumn(Alias.MailSender.Sort) + "=" + MapColumn(Alias.MailSender.Sort) + "-1 WHERE " + MapColumn(Alias.MailSender.Sort) + "<=" + toSort + " AND " + MapColumn(Alias.MailSender.Sort) + " > " + fromSort;
                Command.ExecuteNonQuery();
                #endregion

                #region from的排序值设置为toSort
                Command.CommandText = "UPDATE " + MapTable(Alias.Objects.MailSender) + " SET " + MapColumn(Alias.MailSender.Sort) + "=" + toSort + " WHERE " + MapColumn(Alias.MailSender.InnerID) + "=?";
                Command.ExecuteNonQuery();
                #endregion
            }
        }
        public void Delete(int id)
        {
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.MailSender.InnerID, MapType(Alias.MailSender.InnerID)).Value = id;

            StringBuilder sql = new StringBuilder("DELETE FROM " + MapTable(Alias.Objects.MailSender) + " WHERE " + MapColumn(Alias.MailSender.InnerID) + "=?");

            Command.CommandText = sql.ToString();
            Command.ExecuteNonQuery();
        }
        public DataTable List(int page, int pageSize, string condition, string sort)
        {
            DataTable result = new DataTable();
            result.Columns.Add(Alias.MailSender.ID, MapCSType(Alias.MailSender.ID));
            result.Columns.Add(Alias.MailSender.InnerID, MapCSType(Alias.MailSender.InnerID));
            result.Columns.Add(Alias.MailSender.Name, MapCSType(Alias.MailSender.Name));
            result.Columns.Add(Alias.MailSender.Email, MapCSType(Alias.MailSender.Email));
            result.Columns.Add(Alias.MailSender.Account, MapCSType(Alias.MailSender.Account));
            result.Columns.Add(Alias.MailSender.Password, MapCSType(Alias.MailSender.Password));
            result.Columns.Add(Alias.MailSender.SMTPServer, MapCSType(Alias.MailSender.SMTPServer));
            result.Columns.Add(Alias.MailSender.EnableSsl, MapCSType(Alias.MailSender.EnableSsl));
            result.Columns.Add(Alias.MailSender.Port, MapCSType(Alias.MailSender.Port));
            result.Columns.Add(Alias.MailSender.CreatorID, MapCSType(Alias.MailSender.CreatorID));
            result.Columns.Add(Alias.MailSender.Creator, MapCSType(Alias.MailSender.Creator));
            result.Columns.Add(Alias.MailSender.CreateTime, MapCSType(Alias.MailSender.CreateTime));
            result.Columns.Add(Alias.MailSender.UpdatorID, MapCSType(Alias.MailSender.UpdatorID));
            result.Columns.Add(Alias.MailSender.Updator, MapCSType(Alias.MailSender.Updator));
            result.Columns.Add(Alias.MailSender.UpdateTime, MapCSType(Alias.MailSender.UpdateTime));

            #region 条件
            Command.Parameters.Clear();
            string parenthesis = String.Empty;
            string join = String.Empty;
            StringBuilder where = new StringBuilder();
            if (!String.IsNullOrEmpty(condition))
            {
                where.Append(" WHERE ");
                where.Append("(" + condition + ")");

                ParseCondition(where, "a");
            }
            #endregion

            #region 排序
            if (!String.IsNullOrEmpty(sort))
                sort = " ORDER BY " + ParseSort(sort, "a");
            else
                sort = " ORDER BY a." + MapColumn(Alias.MailSender.Sort) + " ASC";
            #endregion

            #region 构造SQL语句
            StringBuilder sql = new StringBuilder("SELECT");
            if (page > 0 && pageSize > 0)
                sql.Append(" TOP " + (page * pageSize));
            sql.Append(" a." + MapColumn(Alias.MailSender.ID));
            sql.Append(",a." + MapColumn(Alias.MailSender.InnerID));
            sql.Append(",a." + MapColumn(Alias.MailSender.Name));
            sql.Append(",a." + MapColumn(Alias.MailSender.Email));
            sql.Append(",a." + MapColumn(Alias.MailSender.Account));
            sql.Append(",a." + MapColumn(Alias.MailSender.Password));
            sql.Append(",a." + MapColumn(Alias.MailSender.SMTPServer));
            sql.Append(",a." + MapColumn(Alias.MailSender.EnableSsl));
            sql.Append(",a." + MapColumn(Alias.MailSender.Port));
            sql.Append(",a." + MapColumn(Alias.MailSender.CreatorID));
            sql.Append(",a." + MapColumn(Alias.MailSender.Creator));
            sql.Append(",a." + MapColumn(Alias.MailSender.CreateTime));
            sql.Append(",a." + MapColumn(Alias.MailSender.UpdatorID));
            sql.Append(",a." + MapColumn(Alias.MailSender.Updator));
            sql.Append(",a." + MapColumn(Alias.MailSender.UpdateTime));
            sql.Append(" FROM " + parenthesis + MapTable(Alias.Objects.MailSender) + " AS a");
            sql.Append(join + where + sort);
            Command.CommandText = sql.ToString();
            #endregion

            #region 执行SQL语句并获取记录
            int curIndex = 0, rowIndex = 0;
            int startIndex = (page - 1) * pageSize;
            DataReader = Command.ExecuteReader();
            while (DataReader.Read())
            {
                if (pageSize != -1)
                {
                    if (curIndex < startIndex)
                    {
                        curIndex++;
                        continue;
                    }

                    if (rowIndex == pageSize)
                        break;
                }

                DataRow row = result.NewRow();
                int i = 0;
                row[Alias.MailSender.ID] = DataReader[i++];
                row[Alias.MailSender.InnerID] = DataReader[i++];
                row[Alias.MailSender.Name] = DataReader[i++];
                row[Alias.MailSender.Email] = DataReader[i++];
                row[Alias.MailSender.Account] = DataReader[i++];
                row[Alias.MailSender.Password] = DataReader[i++];
                row[Alias.MailSender.SMTPServer] = DataReader[i++];
                row[Alias.MailSender.EnableSsl] = DataReader[i++];
                row[Alias.MailSender.Port] = DataReader[i++];
                row[Alias.MailSender.CreatorID] = DataReader[i] == DBNull.Value ? Guid.Empty : DataReader[i]; i++;
                row[Alias.MailSender.Creator] = DataReader[i++];
                row[Alias.MailSender.CreateTime] = DataReader[i++];
                row[Alias.MailSender.UpdatorID] = DataReader[i] == DBNull.Value ? Guid.Empty : DataReader[i]; i++;
                row[Alias.MailSender.Updator] = DataReader[i++];
                row[Alias.MailSender.UpdateTime] = DataReader[i++];
                result.Rows.Add(row);

                rowIndex++;
            }
            DataReader.Close();
            #endregion

            return result;
        }
        public int GetRecordCount(string condition)
        {
            #region 条件
            Command.Parameters.Clear();
            string parenthesis = String.Empty;
            string join = String.Empty;
            StringBuilder where = new StringBuilder();
            if (!String.IsNullOrEmpty(condition))
            {
                where.Append(" WHERE ");
                where.Append("(" + condition + ")");

                ParseCondition(where, "a");
            }
            #endregion

            Command.CommandText = "SELECT COUNT(*) FROM " + parenthesis + MapTable(Alias.Objects.MailSender) + " AS a" + join + where;
            return Convert.ToInt32(Command.ExecuteScalar());
        }

        public DataTable SenderList()
        {
            DataTable result = new DataTable();
            result.Columns.Add(Alias.MailSender.Creator, MapCSType(Alias.MailSender.Creator));
            result.Columns.Add(Alias.MailSender.CreatorID, MapCSType(Alias.MailSender.CreatorID));

            Command.CommandText = "SELECT DISTINCT " + MapColumn(Alias.MailSender.Creator) + "," + MapColumn(Alias.MailSender.CreatorID) + " FROM " + MapTable(Alias.Objects.MailSender);
            DataReader = Command.ExecuteReader();
            while (DataReader.Read())
            {
                DataRow row = result.NewRow();
                row[Alias.MailSender.Creator] = DataReader[0];
                row[Alias.MailSender.CreatorID] = DataReader[1];
                result.Rows.Add(row);
            }
            DataReader.Close();

            return result;
        }

        public void Sort(string creator)
        {
            List<string> servers = new List<string>();

            DataTable senders = new DataTable();
            senders.Columns.Add(Alias.MailSender.InnerID, MapCSType(Alias.MailSender.InnerID));
            senders.Columns.Add(Alias.MailSender.SMTPServer, MapCSType(Alias.MailSender.SMTPServer));

            #region 读入初始数据
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.MailSender.IsDeleted, OleDbType.Boolean).Value = false;
            if (!String.IsNullOrEmpty(creator))
                Command.Parameters.Add("@" + Alias.MailSender.Creator, OleDbType.VarWChar).Value = creator;

            Command.CommandText = "SELECT " + MapColumn(Alias.MailSender.InnerID) + "," + MapColumn(Alias.MailSender.SMTPServer) + " FROM " + MapTable(Alias.Objects.MailSender) + " WHERE " + MapColumn(Alias.MailSender.IsDeleted) + "=?" + (String.IsNullOrEmpty(creator) ? "" : " AND " + MapColumn(Alias.MailSender.Creator) + "=?");
            DataReader = Command.ExecuteReader();
            while (DataReader.Read())
            {
                DataRow row = senders.NewRow();
                row[Alias.MailSender.InnerID] = DataReader[0];
                row[Alias.MailSender.SMTPServer] = DataReader[1];
                senders.Rows.Add(row);

                if (!servers.Contains(DataReader[1].ToString()))
                    servers.Add(DataReader[1].ToString());
            }
            DataReader.Close();

            if (servers.Count <= 1)
                return;
            #endregion

            #region 进行排序
            DataTable result = senders.Clone();
            while (servers.Count > 1)
            {
                for (int i = 0; i < servers.Count; i++)
                {
                    int j = 0;
                    for (; j < senders.Rows.Count; j++)
                    {
                        if (servers[i] == senders.Rows[j][Alias.MailSender.SMTPServer].ToString())
                            break;
                    }

                    if (j < senders.Rows.Count)
                    {
                        DataRow row = result.NewRow();
                        row[Alias.MailSender.InnerID] = senders.Rows[j][Alias.MailSender.InnerID];
                        row[Alias.MailSender.SMTPServer] = senders.Rows[j][Alias.MailSender.SMTPServer];
                        result.Rows.Add(row);

                        senders.Rows.RemoveAt(j);

                        DataRow[] rows = senders.Select(Alias.MailSender.SMTPServer + "='" + servers[i] + "'");
                        if (rows.Length == 0)
                        {
                            servers.RemoveAt(i);
                            i--;
                        }
                    }
                }
            }

            foreach (DataRow sender in senders.Rows)
            {
                int pos = 0;
                int length = 0;
                int start = 0;
                int end = 0;
                for (int j = 0; j < result.Rows.Count; j++)
                {
                    if (servers[0] == result.Rows[j][Alias.MailSender.SMTPServer].ToString())
                    {
                        end = j;
                        if (end > start)
                        {
                            if (end - start > length)
                            {
                                pos = (end - start) / 2 + start;
                                length = end - start;
                            }
                        }
                        else
                        {
                            pos = 0;
                            length = 0;
                        }

                        end = start = end + 1;
                    }
                }

                DataRow row = result.NewRow();
                row[Alias.MailSender.InnerID] = sender[Alias.MailSender.InnerID];
                row[Alias.MailSender.SMTPServer] = sender[Alias.MailSender.SMTPServer];
                result.Rows.InsertAt(row, pos);
            }
            #endregion

            #region 设置排序结果
            int sort = 1;
            Command.Parameters.Clear();
            Command.Parameters.Add("@" + Alias.MailSender.Sort, OleDbType.Integer);
            Command.Parameters.Add("@" + Alias.MailSender.InnerID, OleDbType.Integer);
            Command.CommandText = "UPDATE " + MapTable(Alias.Objects.MailSender) + " SET " + MapColumn(Alias.MailSender.Sort) + "=?" + " WHERE " + MapColumn(Alias.MailSender.InnerID) + "=?";
            foreach (DataRow row in result.Rows)
            {
                Command.Parameters["@" + Alias.MailSender.Sort].Value = sort;
                Command.Parameters["@" + Alias.MailSender.InnerID].Value = row[Alias.MailSender.InnerID];
                Command.ExecuteNonQuery();

                sort++;
            }
            #endregion
        }
    }
}
